CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`ActivityAlertDetails`(
    IN p_emp_code VARCHAR(255),
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_filter VARCHAR(255),
    IN p_login_time DATETIME,
    IN p_logout_time DATETIME,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    
    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'login_time';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'LoginTime' THEN
        SET p_sort_column = 'login_time';  -- Map "LoginTime" to the correct column name "login_time"
    ELSEIF p_sort_column = 'LogoutTime' THEN
        SET p_sort_column = 'logout_time';  -- Map "LogoutTime" to the correct column name "logout_time"
    ELSEIF p_sort_column = 'SystemStatus' THEN
        SET p_sort_column = 'system_status';  -- Map "SystemStatus" to the correct column name "system_status"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;
   
    DROP TABLE IF EXISTS temp_filtered_results;
   
    CREATE TABLE temp_filtered_results (
        `id` int,
        `emp_code` varchar(255) NOT NULL,
        `login_time` datetime,
        `logout_time` datetime,
        `system_status` varchar(255),
        `ip_address` varchar(15),
        `computer_name` varchar(255),
        `app_version` varchar(255),
        `created_on` timestamp NULL,
        `updated_on` timestamp NULL
    );
	   
    -- Insert filtered results into the temporary table
    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        system_status AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE     
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));

    -- Additional filtering for Login/Logout status
    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        CASE 
            WHEN login_time IS NOT NULL THEN 'Logged In'
            ELSE ''
        END AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE 
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));

    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        CASE 
            WHEN logout_time IS NOT NULL THEN 'Logged Out'
            ELSE ''
        END AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE 
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));   

    -- Filter based on p_filter, p_login_time, and p_logout_time
    CREATE TEMPORARY TABLE temp_filtered2_results AS
    SELECT      
        id,
        emp_code,
        login_time,
        logout_time,
        system_status,
        ip_address,        
        created_on,
        updated_on,
        v_total_records AS Total_Records,
        v_total_pages AS Total_Pages
    FROM
        temp_filtered_results
    WHERE 
        (p_filter IS NULL OR system_status = p_filter)
        AND (p_login_time IS NULL OR DATE(login_time) >= DATE(p_login_time))
        AND (p_logout_time IS NULL OR DATE(login_time) <= DATE(p_logout_time));
                         
    -- Calculate the total number of records from the filtered table
    SELECT COUNT(*) INTO v_total_records FROM temp_filtered2_results;

    -- Calculate the total number of pages
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results
    IF NOT p_ignore_paging THEN
        SET @query = CONCAT('SELECT id AS Id, emp_code AS EmpCode, login_time AS LoginTime, logout_time AS LogoutTime, system_status AS SystemStatus, ip_address AS IpAddress, created_on AS CreatedOn, updated_on AS UpdatedOn, ', 
                            v_total_records, ' AS Total_Records, ', 
                            v_total_pages, ' AS Total_Pages FROM temp_filtered2_results ', 
                            v_sort_query, v_limit_query);
    ELSE
        SET @query = CONCAT('SELECT id AS Id, emp_code AS EmpCode, login_time AS LoginTime, logout_time AS LogoutTime, system_status AS SystemStatus, ip_address AS IpAddress, created_on AS CreatedOn, updated_on AS UpdatedOn, ', 
                            v_total_records, ' AS Total_Records, ', 
                            v_total_pages, ' AS Total_Pages FROM temp_filtered2_results ', 
                            v_sort_query);
    END IF;

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Drop the temporary tables
    DROP TEMPORARY TABLE IF EXISTS temp_filtered_results;
    DROP TEMPORARY TABLE IF EXISTS temp_filtered2_results;
END